﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using KPIS.GERP.GHRMS.DAL;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IBLL; 
using KPIS.DBM;

using KPIS.GERP.GHRMS.DALFactory;

namespace KPIS.GERP.GHRMS.BLL
{
    public class ScoreSubBLL
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);       

        public DataTable select(int prm_seq, Nullable<int> job_site_seq, Nullable<double> score)
        {
            try
            {

                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@prm_seq",prm_seq);
                dbManager.AddParameters(1, "@job_site_seq", job_site_seq);
                dbManager.AddParameters(2, "@score", score);
                dbManager.AddParameters(3, "@record_status", SystemInfo._ActiveRecordStatus);
                
                string sql;
                sql = "SELECT sl.SUB_SCO_LEVEL_SEQ, sl.SUB_SCO_LEVEL_NAME"
                        + ", ssd.SCORE_LEVEL_NAME, ssd.STD_DTLS_SEQ, sl.PRM_SEQ"
                        + ", sl.SCO_MIN, sl.SCO_MAX, sl.PERCEN_PROMOTE, sl.JOB_SITE_SEQ"
                    + " FROM HRM_PRM_SUB_SCORE_LEVELS sl"
                    + " LEFT JOIN HRM_APR_STD_SCORE_DTLS ssd ON sl.STD_DTLS_SEQ = ssd.STD_DTLS_SEQ"
                    + " WHERE sl.RECORD_STATUS = @record_status"
                        + " AND sl.PRM_SEQ = @prm_seq"
                        + (job_site_seq.HasValue ? " AND sl.JOB_SITE_SEQ = @job_site_seq" : "")
                        + (score.HasValue ? " AND (sl.SCO_MIN <= @score AND sl.SCO_MAX >= @score)" : "" );            

                DataSet ds;
                DataTable dt;
                ds = new DataSet();
                dbManager.Open();
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();  
            }
        }

        public void Insert(int prm_seq, int job_site_seq, int std_dtls_seq, string sub_sco_level_name
            , decimal sco_min, decimal sco_max, decimal percen_promote, int created_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(10);

                dbManager.AddParameters(0, "@prm_seq", prm_seq);
                dbManager.AddParameters(1, "@job_site_seq", job_site_seq);
                dbManager.AddParameters(2, "@std_dtls_seq", std_dtls_seq);
                dbManager.AddParameters(3, "@sub_sco_level_name", sub_sco_level_name);
                dbManager.AddParameters(4, "@sco_min", sco_min);
                dbManager.AddParameters(5, "@sco_max", sco_max);
                dbManager.AddParameters(6, "@percen_promote", percen_promote);
                dbManager.AddParameters(7, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(8, "@created_by", created_by);
                dbManager.AddParameters(9, "@created_when", DateTime.Now);

                string strSQL = "INSERT INTO  hrm_prm_sub_score_levels ("
                    + " sub_sco_level_seq" 
                    + ", prm_seq"
                    + ", job_site_seq"
                    + ", std_dtls_seq"
                    + ", sub_sco_level_name"
                    + ", sco_min"
                    + ", sco_max"
                    + ", percen_promote"
                    + ", record_status"
                    + ", created_by"
                    + ", created_when"
                    + ") VALUES ("
                    + " NULL"
                    + ", @prm_seq"
                    + ", @job_site_seq"
                    + ", @std_dtls_seq"
                    + ", @sub_sco_level_name"
                    + ", @sco_min"
                    + ", @sco_max"
                    + ", @percen_promote"
                    + ", @record_status"
                    + ", @created_by"
                    + ", @created_when"
                    + " )";
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.Dispose();
            }
            catch(Exception ex)
            {
                throw;
            }
        }


        public void Update(int prm_seq, int job_site_seq, int std_dtls_seq, string sub_sco_level_name
            , decimal sco_min, decimal sco_max, decimal percen_promote, int updated_by, int sub_sco_level_seq)
        {
            try
            {
                dbManager.CreateParameters(11);

                dbManager.AddParameters(0, "@prm_seq", prm_seq);
                dbManager.AddParameters(1, "@job_site_seq", job_site_seq);
                dbManager.AddParameters(2, "@std_dtls_seq", std_dtls_seq);
                dbManager.AddParameters(3, "@sub_sco_level_name", sub_sco_level_name);
                dbManager.AddParameters(4, "@sco_min", sco_min);
                dbManager.AddParameters(5, "@sco_max", sco_max);
                dbManager.AddParameters(6, "@percen_promote", percen_promote);
                dbManager.AddParameters(7, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(8, "@updated_by", updated_by);
                dbManager.AddParameters(9, "@updated_when", DateTime.Now);
                dbManager.AddParameters(10, "@sub_sco_level_seq", sub_sco_level_seq);

                dbManager.Open();

                string strSQL = "UPDATE hrm_prm_sub_score_levels "
                    + " SET prm_seq= @prm_seq"
                    + ", job_site_seq= @job_site_seq"
                    + ", std_dtls_seq= @std_dtls_seq"
                    + ", sub_sco_level_name= @sub_sco_level_name"
                    + ", sco_min= @sco_min"
                    + ", sco_max= @sco_max"
                    + ", percen_promote= @percen_promote"
                    + ", record_status= @record_status"
                    + ", updated_by= @updated_by"
                    + ", updated_when= @updated_when"
                    + " WHERE sub_sco_level_seq = @sub_sco_level_seq";
                
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
               
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(int sub_sco_level_seq, int UpdateBy)
        {
            try
            {
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@updated_by", UpdateBy);
                dbManager.AddParameters(2, "@updated_when", DateTime.Now);
                dbManager.AddParameters(3, "@sub_sco_level_seq", sub_sco_level_seq);

                dbManager.Open();

                string strSQL = "UPDATE  hrm_prm_sub_score_levels "
                    + " SET "
                    + " record_status= @record_status"
                    + ", updated_by= @updated_by"
                    + ", updated_when= @updated_when"
                    + " WHERE sub_sco_level_seq = @sub_sco_level_seq";                
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                
            }
            catch(Exception ex)
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public bool ChkValidateScore(int std_dtls_seq, double ScoreMin, double ScoreMax)
        {
            dbManager.CreateParameters(2);

            dbManager.AddParameters(0, "@record_status", SystemInfo._ActiveRecordStatus);
            dbManager.AddParameters(1, "@std_dtls_seq", std_dtls_seq);
            //dbManager.AddParameters(2, "@emp_seq", emp_seq);

            DataSet ds = new DataSet();
            dbManager.Open();
            ds = dbManager.ExecuteDataSet(CommandType.Text
                , "SELECT *"
                    + " FROM HRM_APR_STD_SCORE_DTLS"
                    + " WHERE RECORD_STATUS = @record_status"
                        + " AND STD_DTLS_SEQ = @std_dtls_seq");

            if (ds.Tables[0].Rows.Count > 0)
            {
                double Min = double.Parse(ds.Tables[0].Rows[0]["SCORE_MIN"].ToString());
                double Max = double.Parse(ds.Tables[0].Rows[0]["SCORE_MAX"].ToString());
                if (ScoreMin < Min || ScoreMin > Max || ScoreMax < Min || ScoreMax > Max || ScoreMin > ScoreMax)
                    return true;
                else
                    return false;
            }
            else
                return false;
        }

    }
   
}
